package cz.data.common.database.dialect;

import com.alibaba.fastjson.JSONObject;
import cz.data.common.database.DbDialect;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.PreparedStatementCreator;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * 方言抽象类
 */
public abstract class AbstractDbDialect implements DbDialect {

    @Override
    public String columns(String dbName, String schemaName, String tableName) {
        return "select column_name AS COLNAME, ordinal_position AS COLPOSITION, column_default AS DATADEFAULT, is_nullable AS NULLABLE, data_type AS DATATYPE, " +
                "character_maximum_length AS DATALENGTH, numeric_precision AS DATAPRECISION, numeric_scale AS DATASCALE, column_key AS COLKEY, column_comment AS COLCOMMENT " +
                "from information_schema.columns where table_schema = '" + dbName + "' and table_name = '" + tableName + "' order by ordinal_position ";
    }

    @Override
    public String tables(String dbName, String schemaName) {
        return "SELECT table_name AS TABLENAME, table_comment AS TABLECOMMENT, CASE WHEN table_type = 'VIEW' THEN '2' ELSE '1' END TABLETYPE FROM information_schema.tables where table_schema = '" + dbName + "' ";
    }

    @Override
    public String buildPaginationSql(String originalSql, long offset, long count) {
        // 获取 分页实际条数
        StringBuilder sqlBuilder = new StringBuilder(originalSql);
        sqlBuilder.append(" LIMIT ").append(offset).append(" , ").append(count);
        return sqlBuilder.toString();
    }

    @Override
    public String count(String sql) {
        return "SELECT COUNT(*) FROM ( " + sql + " ) TEMP";
    }

    @Override
    public String tableNames(String dbName, String schema) {
        return "SELECT table_name AS TABLENAME FROM information_schema.tables where table_schema = '" + dbName + "' ";
    }

    @Override
    public String columnNames(String dbName, String schema, String tableName) {
        return "SELECT column_name AS COLNAME " +
                "FROM information_schema.columns where table_schema = '" + dbName + "' and table_name = '" + tableName + "' order by ordinal_position ";
    }

    @Override
    public PreparedStatementCreator insertSQL(JSONObject json, String table, String[] insertFields) {
        String fields = String.join(",", insertFields);
        String values = Arrays.stream(insertFields).map(f -> "?").collect(Collectors.joining(","));
        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", table, fields, values);
        return con -> {
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            PreparedStatement ps = con.prepareStatement(sql);
            int i = 0;
            for (String field : insertFields) {
                setObjectOrNULL(ps, ++i, json.get(field));
            }
            return ps;
        };
    }

    @Override
    public PreparedStatementCreator batchInsertSQL(List<JSONObject> records, String table, String[] insertFields) {
        String fields = String.join(",", insertFields);
        String values = Arrays.stream(insertFields).map(f -> "?").collect(Collectors.joining(","));
        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", table, fields, values);

        return conn -> {
            conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            PreparedStatement ps = conn.prepareStatement(sql);
            for (JSONObject record : records) {
                int i = 0;
                for (String field : insertFields) {
                    setObjectOrNULL(ps, ++i, record.get(field));
                }
                ps.addBatch();
            }
            return ps;
        };
    }

    @Override
    public PreparedStatementCreator updateSQL(String table, JSONObject json, String[] updateFields, String[] primaryKeys) {
        boolean anyMatch = Arrays.stream(updateFields).map(json::get).anyMatch(Objects::nonNull);
        if (!anyMatch) {
            throw new IllegalArgumentException("没有需要更新的字段");
        }
        StringBuilder statement = new StringBuilder("UPDATE ").append(table).append(" SET ");
        String setStatement = Arrays.stream(updateFields)
                .filter(f -> Objects.nonNull(json.get(f)))
                .map(f -> f + " = ?")
                .collect(Collectors.joining(","));
        statement.append(setStatement).append(" WHERE ");
        String whereStatement = Arrays.stream(primaryKeys)
                .map(key -> key + " = ?")
                .collect(Collectors.joining(" AND "));
        statement.append(whereStatement);
        String sql = statement.toString();
        return con -> {
            con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            PreparedStatement ps = con.prepareStatement(sql);
            int index = 0;
            for (String field : updateFields) {
                Object value = json.get(field);
                if (Objects.nonNull(value)) {
                    ps.setObject(++index, value);
                }
            }
            for (String primaryKey : primaryKeys) {
                ps.setObject(++index, json.get(primaryKey));
            }
            return ps;
        };
    }

    public static String formatDataType(String dataType) {
        if (StringUtils.contains(dataType, "(")
                && StringUtils.contains(dataType, ")")) {
            dataType = dataType.substring(0, dataType.indexOf("("));
        }
        return dataType;
    }

    public static void setObjectOrNULL(PreparedStatement ps, int index, Object value) throws SQLException {
        if (value == null) {
            ps.setNull(index, Types.NULL);
        } else {
            ps.setObject(index, value);
        }
    }
}
